﻿namespace SRM.DataObjects
{
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.ComponentModel;
    using System.Data;
    using System.Data.SqlClient;

    /// <summary>
    /// Summary description for SRM_SkillSet
    /// </summary>
    [DataObjectAttribute(true)]
    public class SRM_SkillSet
    {

        [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
        public static DataTable GetSkillSet(string applicantKey)
        {
            DataTable dtResult = null;

            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                DataSet ds = new DataSet();
                SqlCommand cmd = conn.CreateCommand();
                SqlDataAdapter da = new SqlDataAdapter(cmd);

                cmd.CommandText = string.Format(@"SELECT  sk.SkillSetKey, 
        sk.ApplicantKey, 
        skc.DisplayText AS 'SkillCategoryDesc',
        skh.DisplayText AS 'SkillHeaderDesc',
        skd.DisplayText AS 'SkillDetailDesc',
        sk.Others,
        sk.SkillCategory, 
        sk.SkillHeader,
        sk.SkillDetail
FROM    SRM_ApplicantSkillSet_Tbl sk
LEFT JOIN 
        SAP_Code skc 
        ON  skc.CodeTypeId = 'SKC' 
        AND skc.Id = sk.SkillCategory
LEFT JOIN 
        SAP_Code skh 
        ON  skh.CodeTypeId = 'SKH' 
        AND skh.Id = sk.SkillHeader
        AND skh.ParentId1 =  sk.SkillCategory
LEFT JOIN 
        SAP_Code skd 
        ON  skd.CodeTypeId = 'SKD' 
        AND skd.Id = sk.SkillDetail
        AND skd.ParentId1 =  sk.SkillCategory
        AND skd.ParentId2 =  sk.SkillHeader
WHERE   sk.ApplicantKey = {0};", applicantKey);
                da.Fill(ds);

                dtResult = ds.Tables[0];
            }

            return dtResult;
        } // public static DataTable GetSkillSet(string applicantKey)

        [DataObjectMethodAttribute(DataObjectMethodType.Insert, true)]
        public static void InsertSkillSet(string applicantKey,
            string skillCategory, string skillHeader, string skillDetail, string skillOthers)
        {
            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "INSERT INTO SRM_ApplicantSkillSet_Tbl (ApplicantKey, SkillCategory, SkillHeader, SkillDetail, Others) VALUES (@ApplicantKey, @SkillCategory, @SkillHeader, @SkillDetail, @Others);";
                cmd.Parameters.AddWithValue("@ApplicantKey", applicantKey);
                cmd.Parameters.AddWithValue("@SkillCategory", skillCategory);
                //cmd.Parameters.AddWithValue("@SkillHeader", skillHeader);
                //cmd.Parameters.AddWithValue("@SkillDetail", skillDetail);
                
                cmd.Parameters.AddWithValue("@SkillHeader", Help.GetValueOrDBNull(skillHeader));
                cmd.Parameters.AddWithValue("@skillDetail", Help.GetValueOrDBNull(skillDetail));
                cmd.Parameters.AddWithValue("@Others", Help.GetValueOrDBNull(skillOthers));

                conn.Open();
                cmd.ExecuteNonQuery();
            }
        } // public static void InsertSkillSet



        [DataObjectMethodAttribute(DataObjectMethodType.Delete, true)]
        public static void DeleteSkillSet(string skillSetKey)
        {
            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                SqlCommand cmd = conn.CreateCommand();

                cmd.CommandText = "DELETE FROM SRM_ApplicantSkillSet_Tbl WHERE SkillSetKey = @SkillSetKey;";
                cmd.Parameters.AddWithValue("@skillSetKey", skillSetKey);

                conn.Open();
                cmd.ExecuteNonQuery();
            }
        } // public static void DeleteSkillSet



        [DataObjectMethodAttribute(DataObjectMethodType.Update, true)]
        public static void UpdateSkillSet(string skillSetKey, 
            string skillCategory, string skillHeader, string skillDetail, string skillOthers)
        {
            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                SqlCommand cmd = conn.CreateCommand();

                cmd.CommandText = @"
UPDATE  SRM_ApplicantSkillSet_Tbl 
SET     SkillCategory = @SkillCategory, 
        SkillHeader = @SkillHeader, 
        SkillDetail = @skillDetail,
        Others = @skillOthers
WHERE   SkillSetKey = @skillSetKey;";

                cmd.Parameters.AddWithValue("@SkillSetKey", skillSetKey);
                cmd.Parameters.AddWithValue("@SkillCategory", skillCategory);
                cmd.Parameters.AddWithValue("@SkillHeader", Help.GetValueOrDBNull(skillHeader));
                cmd.Parameters.AddWithValue("@skillDetail", Help.GetValueOrDBNull(skillDetail));
                cmd.Parameters.AddWithValue("@skillOthers", Help.GetValueOrDBNull(skillOthers));

                conn.Open();
                cmd.ExecuteNonQuery();
            }
        } // public static void UpdateSkillSet

    }
}